package com.lolong.hic;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import com.lolong.model.Establishment;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteConnector extends SQLiteOpenHelper {

	private static final String TAG = "SQLiteConnector";

	private static String DATABASE_PATH = "";
	private static String DATABASE_NAME = "HCILolongDB";
	private static int DATABASE_VERSION = 1;

	public SQLiteDatabase myDatabase;
	private final Context myContext;
	
	Cursor establishmentNamesCursor, favoritesCursor, establishmentFloorCursor, 
	establishmentLandmarkCursor, establishmentPriceRangeCursor, establishmentGetFavoriteCursor,
	establishmentSetFavoriteCursor;

	public SQLiteConnector(Context context) {

		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.myContext = context;
		DATABASE_PATH = "/data/data/" + context.getPackageName() + "/" + "databases/";

		try {
			createDatabase();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void createDatabase() throws IOException {

		boolean databaseExist = checkDatabase();

		if (databaseExist) {
			// Do nothing - database already exist
		} else {
			this.getReadableDatabase();
			try {
				copyDatabase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	private boolean checkDatabase() {

		SQLiteDatabase checkDatabase = null;

		try {

			String pathToDatabase = DATABASE_PATH + DATABASE_NAME;
			checkDatabase = SQLiteDatabase.openDatabase(pathToDatabase, null,
					SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e) {

		}

		if (checkDatabase != null) {
			checkDatabase.close();
		}

		return checkDatabase != null ? true : false;
	}

	private void copyDatabase() throws IOException {

		InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

		Log.d(TAG, "found the database");
		
		String outFileName = DATABASE_PATH + DATABASE_NAME;

		OutputStream myOutput = new FileOutputStream(outFileName);

		byte[] buffer = new byte[2048];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDatabase() throws SQLException {

		String myPath = DATABASE_PATH + DATABASE_NAME;
		myDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	}

	@Override
	public synchronized void close() {

		if (myDatabase != null) {
			myDatabase.close();
		}

		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}
	
	public Cursor rawQuery(String sql, String[] selectionArgs) {
		
		return myDatabase.rawQuery(sql, selectionArgs);
	}

	public String getLocation() {

		String[] columns = new String[] { "name", "location" };
		Cursor cursor = myDatabase.query("Establishments", columns, null, null, null,
				null, null);
		String location = "";

		int index_name = cursor.getColumnIndex("name");
		int index_location = cursor.getColumnIndex("location");

		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			if (cursor.getString(index_name).toString().equalsIgnoreCase("Artwork")) {
				location = "" + cursor.getString(index_location).toString();
				cursor.moveToLast();
				cursor.moveToNext();
			}
		}

		return location;
	}
	
	public ArrayList<String> getEstablishmentNames(String type) {
		
		ArrayList<String> namesArrayList = new ArrayList<String>();
		
		openDatabase();
		
		establishmentNamesCursor = myDatabase.rawQuery("SELECT estab_name FROM Establishment WHERE estab_type='" + type + "'", null);
		establishmentNamesCursor.moveToFirst();
		
		if(establishmentNamesCursor != null) {
			do {
				if(establishmentNamesCursor.getCount() < 1) {
					namesArrayList.clear();
				} else {
					namesArrayList.add(establishmentNamesCursor.getString(establishmentNamesCursor.getColumnIndex("estab_name")));					
				}
			} while(establishmentNamesCursor.moveToNext() != false);
		}
		
		establishmentNamesCursor.close();
		close();
		
		return namesArrayList;
	}
	
	public ArrayList<String> getFavorites() {
		
		ArrayList<String> favorites = new ArrayList<String>();
		
		openDatabase();
		
		favoritesCursor = myDatabase.rawQuery("SELECT estab_name FROM Establishment WHERE estab_isFavorite=1", null);
		favoritesCursor.moveToFirst();
		
		if(favoritesCursor != null) {
			do {
				if(favoritesCursor.getCount() < 1) {
					
				} else {
					favorites.add(favoritesCursor.getString(favoritesCursor.getColumnIndex("estab_name")));
				}
			} while(favoritesCursor.moveToNext() != false);
		}
		
		
		favoritesCursor.close();
		close();
		
		
		return favorites;
		
	}
	

	public ArrayList<String> getEstablishmentInfo(String name) {
		
		ArrayList<String> infoArrayList = new ArrayList<String>();
		
		openDatabase();
		
		//Floor
		establishmentFloorCursor = myDatabase.rawQuery("SELECT estab_floor FROM Establishment WHERE estab_name='" + name + "'", null);
		establishmentFloorCursor.moveToFirst();
		
		if(establishmentFloorCursor != null) {
			do {
				if(establishmentFloorCursor.getCount() < 1) {
					infoArrayList.clear();
				} else {
					infoArrayList.add(establishmentFloorCursor.getString(establishmentFloorCursor.getColumnIndex("estab_floor")));					
				}
			} while(establishmentFloorCursor.moveToNext() != false);
		}
		
		establishmentFloorCursor.close();
		
		//Landmark
		establishmentLandmarkCursor = myDatabase.rawQuery("SELECT estab_landmark FROM Establishment WHERE estab_name='" + name + "'", null);
		establishmentLandmarkCursor.moveToFirst();
		
		if(establishmentLandmarkCursor != null) {
			do {
				if(establishmentLandmarkCursor.getCount() < 1) {
					infoArrayList.clear();
				} else {
					infoArrayList.add(establishmentLandmarkCursor.getString(establishmentLandmarkCursor.getColumnIndex("estab_landmark")));					
				}
			} while(establishmentLandmarkCursor.moveToNext() != false);
		}
		
		establishmentLandmarkCursor.close();
		
		//Price Range
		establishmentPriceRangeCursor = myDatabase.rawQuery("SELECT estab_price_range FROM Establishment WHERE estab_name='" + name + "'", null);
		
		establishmentPriceRangeCursor.moveToFirst();
		
		if(establishmentPriceRangeCursor != null) {
			do {
				if(establishmentPriceRangeCursor.getCount() < 1) {
					infoArrayList.clear();
				} else {
					infoArrayList.add(establishmentPriceRangeCursor.getString(establishmentPriceRangeCursor.getColumnIndex("estab_price_range")));					
				}
			} while(establishmentPriceRangeCursor.moveToNext() != false);
		}
		
		establishmentPriceRangeCursor.close();
		close();
		
		return infoArrayList;
	}
	
	public int getIsFavorite(String name) {
		
		int isFavorite = 0;
		int initFav = 0;
		
		openDatabase();
		
		establishmentGetFavoriteCursor = myDatabase.rawQuery("SELECT estab_isFavorite FROM Establishment WHERE estab_name='" + name + "'", null);

		establishmentGetFavoriteCursor.moveToFirst();
		
		if(establishmentGetFavoriteCursor.getCount() > 0) {
			
			initFav = establishmentGetFavoriteCursor.getInt(establishmentGetFavoriteCursor.getColumnIndex("estab_isFavorite"));
			
			do {
				if(initFav == 1) {
					isFavorite = 1;
				} else {
					isFavorite = 0;
				}
			} while(establishmentGetFavoriteCursor.moveToNext() != false);
		}
		
		close();
		
		return isFavorite;
	}
	
	public void setFavorite(String name) {
		if(getIsFavorite(name) == 0) {
			openDatabase();
			myDatabase.execSQL("UPDATE Establishment SET estab_isFavorite='1' WHERE estab_name='" + name + "'");
		} else {
			openDatabase();
			myDatabase.execSQL("UPDATE Establishment SET estab_isFavorite='0' WHERE estab_name='" + name + "'");
		}
		close();
	}
}
